1. Field of the Invention
The present invention generally relates to query processing and, more particularly, to creating query conditions for queries against data in a database using predefined query objects.
2. Description of the Related Art
Databases are computerized information storage and retrieval systems. The most prevalent type of database is the relational database, a tabular database in which data is defined so that it can be reorganized and accessed in a number of different ways. A distributed database is one that can be dispersed or replicated among different points in a network. An object-oriented programming database is one that is congruent with the data defined in object classes and subclasses.
Regardless of the particular architecture, a database management system (DBMS) can be structured to support a variety of different types of operations for a requesting entity (e.g., an application, the operating system or an end user). Such operations can be configured to retrieve, add, modify and delete information being stored and managed by the DBMS. Standard database access methods support these operations using high-level query languages, such as the Structured Query Language (SQL). The term “query” denominates a set of commands that cause execution of operations for processing data from a stored database. For instance, SQL supports four types of query operations, i.e., SELECT, INSERT, UPDATE and DELETE. A SELECT operation retrieves data from a database, an INSERT operation adds new data to a database, an UPDATE operation modifies data in a database and a DELETE operation removes data from a database.
Any requesting entity, including applications, operating systems and, at the highest level, users, can issue queries against data in a database to obtain required information. Queries may be predefined (i.e., hard coded as part of an application) or generated in response to input (e.g., user input). Queries may include both an explicit specification of result fields for which data is to be returned upon execution of the queries, and criteria used for selection of the data. The data selection criteria are generally represented as query conditions that serve to filter the data returned for the result fields upon execution of the query. Accordingly, a query may be thought of as group of filters put together to sift out only the result field data of interest.
One common technique in query condition building consists in defining subqueries that are configured to define suitable data selection criteria. More specifically, for a given outer query an inner query, or subquery, can be defined such that a corresponding result set of the subquery is suitable to filter the data returned for the result fields of the outer query. In SQL, this can be performed using a so-called IN condition that links a field of a query condition of the given outer query to the subquery. Thus, by executing the subquery valid values for the field of the query condition can be identified. Such a subquery is particularly useful in cases where the valid values need to be retrieved from a different database table than the data that is to be returned for the result field(s) of the given outer query.
However, several difficulties occur in creating and using subqueries as query conditions for SQL queries against underlying databases. First, the users generally need some knowledge of the layout of an underlying database and of SQL to be able to accurately create a subquery for a given outer SQL query. This can be difficult and is error-prone if the outer SQL query and/or the subquery are complex. Furthermore, while a data type check may be performed on each query condition of the outer SQL query, it is not determined whether the values included with a retrieved query result for the subquery are suitable for an associated field of the outer SQL query. For instance, assume that a “patient_id” field of a query condition of a given outer query requires integer values and that an associated subquery returns integer values for a “weight” field. In this case, the data types of the “patient_id” field and the “weight” field are compatible and the outer SQL query is validated. However, a result set obtained in executing the outer SQL query may not be useful as the weight values may not be suitable for the “patient_id” field. Thus, merely confirming that the data type of the returned result set (i.e., weight values, which are integer values) corresponds to the data type of the outer query does not ensure that the returned values are, in fact, the values needed (since weight values are not patient IDs). Thus, mere data type checking is inadequate. Moreover, the subquery must return values only for a result field that matches the field of the query condition to which the subquery is linked. For instance, assume that the subquery returns values for the “patient_id” field and for another result field such as a “LastName” result field or the “weight” field as described above. In this case, the subquery would return too many output values and therefore the outer SQL query would result in an error when being executed.
Therefore, there is a need for an efficient technique for processing queries that include subqueries.